# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/topics/item-pipeline.html
import MySQLdb
import MySQLdb.cursors
from scrapy import log
from twisted.enterprise import adbapi

from jobspider.items import JobItem, JobDetailItem, CompanyInfoItem


class JobspiderPipeline(object):
    def process_item(self, item, spider):
        return item


class MySQLStorePipeline(object):
    """docstring for MySQLstor"""

    def __init__(self):
        self.dbpool = adbapi.ConnectionPool('MySQLdb',
                                            host='127.0.0.1',
                                            db='job_monitor',
                                            user='root',
                                            passwd='root',
                                            cursorclass=MySQLdb.cursors.DictCursor,
                                            charset='utf8',
                                            use_unicode=True
                                            )

    def process_item(self, item, spider):
        # print spider
        # run db query in thread pool
        if spider.name == "job51Spider":
            query = self.dbpool.runInteraction(self.job51_insert, item)
            query.addErrback(self.handle_error)
        elif spider.name == "job58Spider":
            query = self.dbpool.runInteraction(self.job58_insert, item)
            query.addErrback(self.handle_error)
        else:
            query = self.dbpool.runInteraction(self.job51_insert, item)
            query.addErrback(self.handle_error)
        return item

    def job51_insert(self, tx, item):

        if isinstance(item, JobItem):
            tx.execute(
                "insert into job (job_id, job_url, job_name, company_name, company_detail_url, city, district, job_detail_address, salary, publish_time, query_time, query_type) values (null,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                (
                    item['job_url'],
                    item['job_name'],
                    item['company_name'],
                    item['company_detail_url'],
                    item['city'],
                    item['district'],
                    item['job_detail_address'],
                    item['salary'],
                    item['publish_time'],
                    item['query_time'],
                    item['query_type']
                )
            )

        if isinstance(item, JobDetailItem):
            tx.execute(
                "insert into job_detail (job_deatil_id, job_url, experience, education_level, recruit_num, work_welfare, occupation_info, job_contact_information,query_time) values (null, %s, %s, %s, %s, %s, %s, %s, %s)",
                (
                    item['job_url'],
                    item['experience'],
                    item['education_level'],
                    item['recruit_num'],
                    item['work_welfare'],
                    item['occupation_info'],
                    item['job_contact_information'],
                    item['query_time']
                )
            )

        if isinstance(item, CompanyInfoItem):
            tx.execute(
                "insert into companyinfo (company_id, company_name, company_detail_url,conpany_type, conpany_size, company_orientation, company_info) values (null, %s, %s, %s, %s, %s, %s)",
                (
                    item['company_name'],
                    item['company_detail_url'],
                    item['conpany_type'],
                    item['conpany_size'],
                    item['company_orientation'],
                    item['company_info']
                )
            )

    def job58_insert(self, tx, item):

        if isinstance(item, JobItem):
            tx.execute(
                "insert into job (job_id, job_url, job_name, company_name, company_detail_url, city, district, job_detail_address, salary, publish_time, query_time, query_type) values (null,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                (
                    item['job_url'],
                    item['job_name'],
                    item['company_name'],
                    item['company_detail_url'],
                    item['city'],
                    item['district'],
                    item['job_detail_address'],
                    item['salary'],
                    item['publish_time'],
                    item['query_time'],
                    item['query_type']
                )
            )
        if isinstance(item, CompanyInfoItem):
            tx.execute(
                "insert into companyinfo (company_id, company_name, company_detail_url, company_level) values (null, %s, %s, %s)",
                (
                    item['company_name'],
                    item['company_detail_url'],
                    item['company_level']
                )
            )

    def handle_error(self, e):
        log.err(e)
